const db = require('../db'); // 引入数据库连接
const dayjs = require('dayjs');
const timezone = require('dayjs/plugin/timezone');
const utc = require('dayjs/plugin/utc');  // 时区相关操作需要使用 `utc` 插件

// 使用插件
dayjs.extend(utc);
dayjs.extend(timezone);
// 获取所有商品
const getAllProducts = async () => {
    const query = `
        SELECT p.product_id, p.name, p.description, p.price, p.stock, p.image_url, p.category, p.created_at, p.updated_at, p.user_id, u.username
        FROM products p
        JOIN users u ON p.user_id = u.user_id
        ORDER BY p.created_at DESC  -- 按照创建时间降序排列
    `;
    try {
        const [rows] = await db.promise().execute(query);

        // 将每个商品的图片链接从 JSON 字符串转换为数组
        const products = rows.map(row => {
            let images = [];
            if (row.image_url) {
                try {
                    images = JSON.parse(row.image_url);
                } catch (error) {
                    console.error(`Error parsing image_url for product_id ${row.product_id}:`, error);
                    images = [];  // 如果解析失败，保持为空数组
                }
            }
            // 格式化 created_at 和 updated_at
            const formattedCreatedAt = dayjs(row.created_at).local().format('YYYY-MM-DD HH:mm');
            const formattedUpdatedAt = dayjs(row.updated_at).local().format('YYYY-MM-DD HH:mm');
            return {
                ...row, images, created_at: formattedCreatedAt,
                updated_at: formattedUpdatedAt
            };
        });

        return products;
    } catch (error) {
        console.error('Database error:', error);
        throw new Error('Error fetching products');
    }
};
// 获取当前用户所有商品
const getProductsByUserId = async (userId) => {
    // 检查 userId 是否为 undefined
    if (userId === undefined) {
        throw new Error("Invalid parameter: userId is undefined");
    }

    const query = `
        SELECT p.product_id, p.name, p.description, p.price, p.stock, p.image_url, p.category, p.created_at, p.updated_at, p.user_id, u.username
        FROM products p
        JOIN users u ON p.user_id = u.user_id
        WHERE p.user_id = ?
        ORDER BY p.created_at DESC
    `;

    try {
        const [rows] = await db.promise().execute(query, [userId]);
        // console.log(rows);  // 打印所有商品信息

        // 将每个商品的图片链接从 JSON 字符串转换为数组
        const products = rows.map(row => {
            let images = [];
            if (row.image_url) {
                try {
                    images = JSON.parse(row.image_url);
                } catch (error) {
                    console.error(`Error parsing image_url for product_id ${row.product_id}:`, error);
                    images = [];  // 如果解析失败，保持为空数组
                }
            }
            // 格式化 created_at 和 updated_at
            const formattedCreatedAt = dayjs(row.created_at).local().format('YYYY-MM-DD HH:mm');
            const formattedUpdatedAt = dayjs(row.updated_at).local().format('YYYY-MM-DD HH:mm');
            return {
                ...row, images, created_at: formattedCreatedAt,
                updated_at: formattedUpdatedAt
            };
        });

        return products;
    } catch (error) {
        console.error('Database error:', error);
        throw new Error('Error fetching products');
    }

};
//退款
const setproduct = async (productId) => {
    console.log(productId);
    const [rows] = await db.promise().query(
        `UPDATE orders
         SET order_status = 'cancelled'
         WHERE order_id = ?`,
        [productId]
    );
    return rows;

}
// 取消拼团
const cancelGroupBuy = async (groupbuy_id) => {
    console.log(groupbuy_id, "取消拼团");
    const [result] = await db.promise().execute(
        `DELETE FROM groupbuy WHERE groupbuy_id = ?`,
        [groupbuy_id]
    );
    return result;
};
// 获取商品详情
const getProductById = async (productId) => {
    const query = `
        SELECT p.product_id, p.name, p.description, p.price, p.stock, p.image_url, p.category, p.created_at, p.updated_at, p.user_id, u.username
        FROM products p
        JOIN users u ON p.user_id = u.user_id
        WHERE p.product_id = ?;
    `;

    try {
        const [rows] = await db.promise().execute(query, [productId]);

        if (rows.length === 0) {
            return null;  // 商品未找到
        }

        // 将商品图片链接从 JSON 字符串转换为数组
        const product = rows[0];
        let images = [];
        if (product.image_url) {
            try {
                images = JSON.parse(product.image_url);
            } catch (error) {
                console.error(`Error parsing image_url for product_id ${product.product_id}:`, error);
                images = [];
            }
        }

        // 格式化时间字段
        const formattedCreatedAt = dayjs(product.created_at).local().format('YYYY-MM-DD HH:mm');
        const formattedUpdatedAt = dayjs(product.updated_at).local().format('YYYY-MM-DD HH:mm');

        // 返回商品详情
        return { ...product, images, created_at: formattedCreatedAt, updated_at: formattedUpdatedAt };
    } catch (error) {
        console.error('Database error:', error);
        throw new Error('Error fetching product by ID');
    }
};
// 查看商品是否已发起拼团
const getProd = async (userId, product_id) => {


    const query = `
     SELECT * 
FROM groupbuy g
WHERE g.creator_user_id = ?
  AND g.product_id = ?
  AND g.current_count != 3
  AND DATE(g.end_time) > CURDATE();
    `;
    const [rows] = await db.promise().execute(query, [userId, product_id]);


    return rows;
};

const getProd2 = async (userId, product_id) => {
    const query = `
    SELECT * 
FROM groupbuy_participants
WHERE groupbuy_id =?
and user_id= ?
   `;
    const [rows] = await db.promise().execute(query, [product_id, userId]);


    return rows;
}

// 添加商品
const addProduct = async (product, userId) => {
    const { name, description, price, stock, category, image_url } = product;

    // 确保字段不是 undefined，如果是则赋值为 null 或空字符串
    const safeName = name !== undefined ? name : null;
    const safeDescription = description !== undefined ? description : null;
    const safePrice = price !== undefined ? price : null;
    const safeStock = stock !== undefined ? stock : null;
    const safeCategory = category !== undefined ? category : null;

    // 如果 image_url 是数组，则将其转化为字符串（JSON 格式）
    const safeImageUrl = image_url !== undefined ? JSON.stringify(image_url) : null;

    const query = `
        INSERT INTO products (name, description, price, stock, category, image_url, user_id)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    `;

    try {
        // 执行插入查询
        const [result] = await db.promise().execute(query, [
            safeName, safeDescription, safePrice, safeStock, safeCategory, safeImageUrl, userId
        ]);
        return result.insertId; // 返回新插入商品的 ID
    } catch (error) {
        console.error('Database error:', error);
        throw new Error('Error adding product');
    }
};


// 更新商品信息
const updateProduct = async (productId, updatedProduct, image_url) => {
    const { name, description, price, stock, category } = updatedProduct;

    // 检查每个字段，如果是 undefined，将其转换为 null
    const updatedName = name === undefined ? null : name;
    const updatedDescription = description === undefined ? null : description;
    const updatedPrice = price === undefined ? null : price;
    const updatedStock = stock === undefined ? null : stock;
    const updatedCategory = category === undefined ? null : category;


    // 如果 image_url 是数组，则将其转化为字符串（JSON 格式）
    // const updatedImageUrls = image_url ? JSON.stringify(image_url) : null;
    const updatedImageUrls = Array.isArray(image_url) && image_url.length > 0 ? JSON.stringify(image_url) : null;
    const query = `
        UPDATE products
        SET name = ?, description = ?, price = ?, stock = ?, category = ?, image_url = ?
        WHERE product_id = ? AND user_id = ?
    `;

    try {
        // 执行数据库更新操作
        await db.promise().execute(query, [
            updatedName, updatedDescription, updatedPrice, updatedStock, updatedCategory, updatedImageUrls, productId, updatedProduct.user_id
        ]);

    } catch (error) {
        console.error('Database error:', error);
        throw new Error('Error updating product');
    }
};


// 删除商品
const deleteProduct = async (productId) => {
    const query = 'DELETE FROM products WHERE product_id = ?';
    try {
        const [result] = await db.promise().execute(query, [productId]);
        return result;  // 返回执行结果，用于控制器判断
    } catch (error) {
        console.error('Database error:', error);
        throw new Error('Error deleting product');
    }
};

// 根据分类获取商品
const getProductsByCategory = async (category) => {
    try {
        // 使用 db.promise() 执行查询，并解构结果
        const [rows] = await db.promise().execute('SELECT * FROM products WHERE category = ? ORDER BY created_at DESC', [category]);

        console.log('Rows:', rows);  // 打印 rows，查看商品数据

        return rows;  // 返回查询结果中的 rows 部分
    } catch (error) {
        console.error('Error executing query:', error);  // 打印具体错误
        throw error;  // 将错误抛出供控制器捕获
    }
};


const getGroupBuyStatus = async (product_id, user_id) => {
    try {
        const [rows] = await db.promise().execute(
            `SELECT g.groupbuy_id, g.product_id, g.group_size, g.current_count, g.status, g.end_time, 
                    g.creator_user_id, g.is_paid, GROUP_CONCAT(p.user_id) AS participants
             FROM groupbuy g
             LEFT JOIN groupbuy_participants p ON g.groupbuy_id = p.groupbuy_id
             WHERE g.product_id = ? AND g.status IN ('waiting', 'in_progress')
             GROUP BY g.groupbuy_id`,
            [product_id]
        );

        if (rows.length === 0) {
            return null; // No active group buy found
        }

        const groupBuyData = rows[0];
        const participants = groupBuyData.participants
            ? groupBuyData.participants.split(',').map(id => id.trim()) // 清理空格
            : [];

        // 调试输出


        // 判断当前用户是否已参与拼团
        const isUserParticipant = participants.includes(String(user_id).trim());
        console.log("用户是否已参与:", isUserParticipant);

        // 格式化结束时间
        const formattedEndTime = dayjs(groupBuyData.end_time).utc().format('YYYY-MM-DD HH:mm:ss');

        return {
            ...groupBuyData,
            end_time: formattedEndTime, // 格式化后的时间
            participants,
            remaining: groupBuyData.group_size - groupBuyData.current_count,
            isUserParticipant
        };
    } catch (error) {
        console.error('数据库查询失败:', error);
        throw new Error('数据库查询失败');
    }
};

// 发起拼团活动
const startGroupBuy = async (product_id, user_id, end_time) => {
    try {
        // 格式化结束时间
        const formattedEndTime = new Date(end_time).toISOString().slice(0, 19).replace('T', ' ');

        // 插入拼团记录
        const [result] = await db.promise().execute(
            `INSERT INTO groupbuy (product_id, creator_user_id, group_size, current_count, end_time, status)
            VALUES (?, ?, 3, 1, ?, 'in_progress')`,
            [product_id, user_id, formattedEndTime]
        );

        const groupbuy_id = result.insertId;

        // 将发起者添加为参与者
        await db.promise().execute(
            `INSERT INTO groupbuy_participants (groupbuy_id, user_id) VALUES (?, ?)`,
            [groupbuy_id, user_id]
        );

        return groupbuy_id;
    } catch (error) {
        console.error('发起拼团失败:', error);
        throw new Error('发起拼团失败');
    }
};

// // 加入拼团
const joinGroupBuy = async (groupbuy_id, user_id) => {
    try {
        // 查询拼团活动的当前状态
        const [group] = await db.promise().execute(
            `SELECT group_size, current_count, status FROM groupbuy WHERE groupbuy_id = ? AND status = 'in_progress'`,
            [groupbuy_id]
        );

        if (group.length === 0) {
            throw new Error('拼团活动不存在或已过期');
        }

        const { group_size, current_count } = group[0];

        // 检查拼团是否已满员
        if (current_count >= group_size) {
            throw new Error('拼团人数已满');
        }

        // 插入参与者
        await db.promise().execute(
            `INSERT INTO groupbuy_participants (groupbuy_id, user_id) VALUES (?, ?)`,
            [groupbuy_id, user_id]
        );

        // 增加当前参与人数
        await db.promise().execute(
            `UPDATE groupbuy SET current_count = current_count + 1 WHERE groupbuy_id = ?`,
            [groupbuy_id]
        );

        // 如果人数达到拼团要求，更新拼团状态为 completed
        if (current_count + 1 >= group_size) {
            await db.promise().execute(
                `UPDATE groupbuy SET status = 'completed', is_paid = 1  WHERE groupbuy_id = ?`,
                [groupbuy_id]
            );
        }

        return { success: true };
    } catch (error) {
        throw new Error(error.message || '加入拼团失败');
    }
};


// 获取拼团参与者列表
const getParticipants = async (groupbuy_id) => {
    try {
        const [rows] = await db.promise().execute(
            `SELECT user_id FROM groupbuy_participants WHERE groupbuy_id = ?`,
            [groupbuy_id]
        );

        return rows.map(row => row.user_id);
    } catch (error) {
        throw new Error('获取参与者失败');
    }
};


module.exports = {
    getGroupBuyStatus,
    startGroupBuy,
    joinGroupBuy,
    getParticipants,
    getAllProducts,
    getProductsByUserId,
    getProductById,
    addProduct,
    updateProduct,
    deleteProduct,
    getProd,
    getProd2,
    getProductsByCategory,
    setproduct,
    cancelGroupBuy
};